using System;
using System.Collections.Generic;
using System.Text;
using NHibernate.Connection;
using NHibernate.Driver;
using NHibernate;
using System.Data;
using System.Collections;
using FutureOpen.Core.func.util;

namespace FutureOpen.Core.func.db
{
    public class FreqItem
    {
        public string name;
        public int freq;
        public FreqItem(string name, int freq)
        {
            this.name = name;
            this.freq = freq;
        }       
    }
    public class MySuggest : MyDBEntity
    {
        public MySuggest()
        {

        }
       
        private static readonly string GET_SCH = "select usr_sch, count(usr_sch) as n from usr where usr_sch like '%{0}%' group by usr_sch order by n desc";
        public List<FreqItem> GetTopSchs(string prefix)
        {
            return GetTopItems(string.Format(GET_SCH, prefix));
        }

        private static readonly string GET_TITLE = "select usr_title, count(usr_title) as n from usr where usr_title like '%{0}%' group by usr_title order by n desc";
        public List<FreqItem> GetTopTitles(string prefix)
        {
            return GetTopItems(string.Format(GET_TITLE, prefix));
        }

        private static readonly string GET_COMPANY = "select usr_company, count(usr_company) as n from usr where usr_company like '%{0}%' group by usr_company order by n desc";
        public List<FreqItem> GetTopCompanies(string prefix)
        {
            return GetTopItems(string.Format(GET_COMPANY, prefix));
        }

        public List<FreqItem> GetTopItems(string sql)
        {
            List<FreqItem> list = new List<FreqItem>();
            NHibernate.SqlCommand.SqlString selectString = new NHibernate.SqlCommand.SqlString(sql);
            
            using (ISession session = base.factory.OpenSession())
            {
                IDbCommand cmd = factory.ConnectionProvider.Driver.GenerateCommand(System.Data.CommandType.Text, selectString, new NHibernate.SqlTypes.SqlType[] { });
                cmd.Connection = session.Connection;
                IDataReader rs = cmd.ExecuteReader();

                while (rs.Read())
                {
                    string str = rs[0].ToString();
                    if (!string.IsNullOrEmpty(str))
                    {
                        FreqItem item = new FreqItem(rs[0].ToString(), Int32.Parse(rs[1].ToString()));
                        list.Add(item);
                    }
                }
            }
            return list;
        }

    }
}
